﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text.RegularExpressions;
using Dapper;
using Just.Library.Util;
using MySql.Data.MySqlClient;
using Xigua.MysqlExtensions;

namespace ConsoleApp.Util
{
    public class DapperHelper_MySql : DapperHelperBase
    {
        public DapperHelper_MySql(string connectionString)
        {
            ConnectionString = connectionString;
        }
        protected override IDbConnection OpenConnection()
        {
            var conn = new MySqlConnection(ConnectionString);
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            return conn;
        }

        #region 分页查询1
        /// <summary>
        /// 数据分页查询  --支持多表查询  
        /// </summary>
        /// <param name="TableName"></param>
        /// <param name="sqlWhere"></param>
        /// <param name="orderBy"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageNo"></param>
        /// <param name="fields"></param>
        /// <param name="parameters"></param>
        /// <returns> table1=总记录数，table2=分页数据  </returns>
        protected DataSet GetDataPageBase(string TableName, string sqlWhere, string orderBy, int pageSize = 10, int pageNo = 1, string fields = "*", object parameters = null)
        {
            DataSet ds = new DataSet();
            if (!string.IsNullOrWhiteSpace(sqlWhere))
            {
                sqlWhere = $" where {ClearAndOr(sqlWhere)}";
            }
            if (!string.IsNullOrWhiteSpace(orderBy))
            {
                orderBy = $" order by {orderBy}";
            }
            TableName = string.IsNullOrWhiteSpace(TableName) ? "table_01" : TableName;

            int begin_index = (pageNo - 1) * pageSize;
            string count_sql = $"select count(*) from {TableName} {sqlWhere} ;";
            string dt_sql = $"select {fields} from {TableName} {sqlWhere} {orderBy} LIMIT {begin_index},{pageSize} ;";
            string sql = $@"
                {count_sql}
                {dt_sql}
            ";
            using (IDbConnection conn = OpenConnection())
            {
                using (var reader = conn.ExecuteReader(sql, parameters, commandType: CommandType))
                {
                    while (!reader.IsClosed)
                    {
                        ds.Tables.Add().Load(reader);
                    }
                }
            }

            return ds;
        }

        /// <summary>
        /// 数据分页查询  --支持多表查询  
        /// </summary>
        /// <param name="TableName">查询的表名</param>
        /// <param name="sqlWhere">sql 查询命令，不要含 where 关键字</param>
        /// <param name="orderBy">排序命令，不要含 order by 关键字</param>
        /// <param name="totalCount">总条数</param>
        /// <param name="fields">字段，默认为*</param>
        /// <param name="pageSize">页大小</param>
        /// <param name="pageNo">页号</param>
        /// <returns></returns>
        public virtual DataTable GetDataPage(string TableName, string sqlWhere, string orderBy, out int totalCount, int pageSize = 10, int pageNo = 1, string fields = "*", object parameters = null)
        {
            DataTable dt = null;
            totalCount = 0;
            DataSet ds = GetDataPageBase(TableName, sqlWhere, orderBy, pageSize, pageNo, fields, parameters);
            if (ds != null && ds.Tables != null && ds.Tables.Count > 0)
            {
                totalCount = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
                dt = ds.Tables[1];
            }
            return dt;
        }

        #endregion

        #region 分页查询

        /// <summary>
        /// dapper通用分页方法
        /// </summary>
        /// <typeparam name="T">泛型集合实体类</typeparam>
        /// <param name="fields">列</param>
        /// <param name="TableName">表</param>
        /// <param name="sqlWhere">条件</param>
        /// <param name="orderBy">排序</param>
        /// <param name="pageIndex">当前页</param>
        /// <param name="pageSize">当前页显示条数</param>
        /// <param name="totalCount">结果集总数</param>
        /// <returns></returns>
        public IEnumerable<T> GetPageList<T>(string TableName, string sqlWhere, string orderBy, out int totalCount, int pageIndex = 1, int pageSize = 10, string fields = "*", object parameters = null)
        {
            int begin_index = (pageIndex - 1) * pageSize;
            string count_sql = $"select count(*) from {TableName} {sqlWhere} ;";
            string dt_sql = $"select {fields} from {TableName} {sqlWhere} {orderBy} LIMIT {begin_index},{pageSize} ;";
            string sql = $@"
                {count_sql}
                {dt_sql}
            ";

            using (IDbConnection conn = OpenConnection())
            {
                using (var reader = conn.QueryMultiple(sql))
                {
                    totalCount = reader.ReadFirst<int>();
                    return reader.Read<T>();
                }
            }
        }

        #endregion

        #region 辅助方法

        /// <summary>
        /// 清除WHERE开头的 AND 和 OR
        /// </summary>
        /// <param name="where">待处理的where子句（不含where ' AND' or ' OR'开头）</param>
        /// <returns></returns>
        private string ClearAndOr(string where) => Regex.Replace(where.TrimStart(), @"^(AND|OR)", "", RegexOptions.IgnoreCase);
		#endregion


		#region MySql InsertOnDuplicateKeyUpdate

		public void InsertOnDuplicateKeyUpdate<T>(List<T> maplist) where T : class
		{

			if (maplist?.Any() ?? false)
			{
				using (var conn = new MySqlConnection(ConnectionString))
				{
					conn.InsertOnDuplicateKeyUpdate(maplist);

				}
			}
		}

		public void InsertOnDuplicateKeyUpdateSplitExcute<T>(int size, List<T> maplist) where T : class
        {
            if (maplist?.Any() ?? false)
            {
                maplist.SplitExecute(size, items =>
                {
                    var retrycount = 3;
                    while (retrycount-- > 0)
                    {
                        try
                        {
                            InsertOnDuplicateKeyUpdate(items);
                            break;
                        }
                        catch (Exception ex)
                        {
                            ConsoleHelper.WriteErrorLine(ex.ToString());
                        }
                    }
                });

            }
        }

		#endregion


		public static List<T> GetListFromKs<T>(string sql, object par = null, int commandTimeout = 180, string connectionname = "ADBTest")
		{
			using (var conn = new MySqlConnection(ConfigHelper.GetConnectionString(connectionname)))
			{
				if (par != null)
				{
					return conn.Query<T>(sql, par, commandTimeout: commandTimeout).ToList();
				}
				else { return conn.Query<T>(sql, commandTimeout: commandTimeout).ToList(); }

			}
		}

	}
}
